package com.b2c.repository.erp;

import com.alibaba.fastjson.JSON;
import com.b2c.entity.result.PagingResponse;
import com.b2c.entity.erp.GoodsCategoryEntity;
import com.b2c.entity.erp.vo.*;
import com.b2c.entity.result.EnumResultVo;
import com.b2c.entity.result.ResultVo;
import com.b2c.repository.Tables;
import com.b2c.entity.erp.ErpGoodsSpecEntity;
import com.b2c.entity.ErpGoodsSpecMoveFromEntiy;
import com.b2c.entity.ErpGoodsStockInfoEntity;
import com.b2c.entity.ErpStockLocationEntity;
import com.b2c.entity.enums.erp.EnumGoodsStockLogSourceType;
import com.b2c.entity.enums.erp.EnumGoodsStockLogType;
import com.b2c.entity.vo.ErpGoodsStockLogsListVo;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.util.StringUtils;

import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 描述：
 * 供应商Repository
 *
 * @author qlp
 * @date 2019-03-21 13:48
 */
@Repository
public class StockRepository {
    private static Logger log = LoggerFactory.getLogger(StockRepository.class);
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 查询总页数
     *
     * @return
     */
    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }

    /**
     * 库存分类查询
     *
     * @return
     */
    public List<GoodsCategoryVo> getCategoryList() {
        List<GoodsCategoryVo> list = new ArrayList<>();

        List<GoodsCategoryEntity> parentList = jdbcTemplate.query("SELECT * FROM " + Tables.ErpGoodsCategory + " WHERE parent_id=0 AND isDelete=0", new BeanPropertyRowMapper<>(GoodsCategoryEntity.class));
        for (var parent: parentList) {
            GoodsCategoryVo parentVo = new GoodsCategoryVo();
            parentVo.setEntity(parent);
            List<GoodsCategoryVo> lists = getSubCategoryVos(parent.getId());
            parentVo.setList(getSubCategoryList(lists));
            list.add(parentVo);
        }



        return list;
    }

    /**
     * 迭代查询库存分类信息
     *
     * @param vos
     * @return
     */
    private List<GoodsCategoryVo> getSubCategoryList(List<GoodsCategoryVo> vos) {
        if (vos != null && vos.size() > 0) {
            for (GoodsCategoryVo vo : vos) {
                vo.setList(getSubCategoryVos(vo.getEntity().getId()));
                getSubCategoryList(vo.getList());
            }
        }
        return vos;
    }

    /**
     * 根据父类id查询子类库存分类
     *
     * @param parentId
     * @return
     */
    private List<GoodsCategoryVo> getSubCategoryVos(Integer parentId) {
        String sql = "SELECT * FROM " + Tables.ErpGoodsCategory + " WHERE parent_id = ? AND isDelete=0";
        List<GoodsCategoryEntity> entities = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(GoodsCategoryEntity.class), parentId);
        List<GoodsCategoryVo> vos = new ArrayList<>();
        for (GoodsCategoryEntity entity : entities) {
            GoodsCategoryVo vo = new GoodsCategoryVo();
            vo.setEntity(entity);
            vos.add(vo);
        }
        return vos;
    }

    /**
     * 添加库存商品分类
     *
     * @param name
     * @param parentId 上上级Id
     * @param id       上级id
     */
    public void addStockCategory(String name, int parentId, int id) {
        String addStockCategory = "INSERT INTO " + Tables.ErpGoodsCategory + " (name,parent_id,path,create_on) VALUES(?,?,?,unix_timestamp(now()))";
        jdbcTemplate.update(addStockCategory, name, id, parentId + "|" + id);
    }

    /**
     * 查询分类对应的下级分类
     *
     * @param id
     * @return
     */
    public Integer getCategorySub(Integer id) {
        String sql = "SELECT IFNULL(count(id),0) FROM " + Tables.ErpGoodsCategory + " WHERE parent_id=? AND isDelete=0";
        return jdbcTemplate.queryForObject(sql, Integer.class, id);
    }

    /**
     * 查询分类对应的商品记录
     *
     * @param categoryId
     * @return
     */
    public Integer getCategoryGoodsSize(Integer categoryId) {
        String sql = "SELECT IFNULL(count(id),0) FROM " + Tables.ErpGoods + " WHERE categoryId=? AND isDelete=0";
        return jdbcTemplate.queryForObject(sql, Integer.class, categoryId);
    }

    public void delStockCategory(Integer id) {
        //删除商品分类
        jdbcTemplate.update("UPDATE " + Tables.ErpGoodsCategory + " SET isDelete=1 WHERE id=?", id);
    }

    private StringBuilder getGoodsListSQL(){
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT SQL_CALC_FOUND_ROWS ");
        sb.append(" g.id,g.image,g.name,g.number,g.status,cate.name as category,g.disable,g.createTime,g.freight,g.sizeImg, ");
        sb.append("g.attr1,g.attr2,g.attr3,g.attr4,g.attr5,g.weight,g.length,g.height,g.width,g.width1,g.width2,g.width3,g.remark,g.salePrice,g.cost_price");
        sb.append(",(SELECT COUNT(0) FROM ").append(Tables.ErpGoodsSpec).append(" WHERE goodsId = g.id ) as specCount ");
//        sb.append(" ,u.name as unitName ");
//        sb.append(" ,g.locationId,sh.name as locationName ");
        sb.append(",IFNULL((SELECT IFNULL(SUM(currentQty),0) FROM erp_goods_stock_info WHERE goodsId=g.id AND isDelete=0),0) AS qty ");
//        sb.append(" IFNULL((select SUM(quantity) from erp_order_item WHERE productId=g.id AND (STATUS=0 OR STATUS=1)),0) qty1" );
        sb.append(" FROM ").append(Tables.ErpGoods).append(" as g ");
        sb.append(" LEFT JOIN ").append(Tables.ErpGoodsCategory).append(" as cate on cate.id=g.categoryId ");
//        sb.append(" LEFT JOIN ").append(Tables.ErpUnit).append(" as u on u.id=g.unitId ");
//        sb.append(" LEFT JOIN ").append(Tables.ErpStockLocation).append(" as sh on sh.id=g.locationId ");
//        sb.append(" LEFT JOIN ").append(Tables.ErpReservoir).append(" as sr on sr.id=g.reservoirId ");
//        sb.append(" LEFT JOIN ").append(Tables.ErpStockLocation).append(" as ss on ss.id=g.shelfId ");

        sb.append(" WHERE g.isDelete=0 ");
//        sb.append("SELECT SQL_CALC_FOUND_ROWS A.id,A.goodsId,A.specName,A.specNumber,A.currentQty,A.disable,B.name,B.number,B.categoryId,B.categoryName,B.locationId,B.locationName,B.reservoirId,B.reservoirName,B.shelfId,B.shelfName ");
//        sb.append("FROM " + Tables.ErpGoodsSpec + " A LEFT JOIN " + Tables.ErpGoods + " B ON A.goodsId = B.id WHERE A.isDelete=0 ");
        return sb;
    }
    /**
     * 商品列表查询
     *
     * @param pageIndex
     * @param pageSize
     * @param number
     * @param categoryId 商品分类id
     * @return
     */
    public PagingResponse<ErpGoodsListVo> getGoodsList(int pageIndex, int pageSize, Integer contactId, String number, Integer categoryId, Integer status, String attr1, String attr4) {
        List<ErpGoodsListVo> list = new ArrayList<>();
        StringBuilder sb = getGoodsListSQL();
        List<Object> params = new ArrayList<>();

        if (!StringUtils.isEmpty(attr1)) {
            sb.append("AND g.attr1 = ? ");
            params.add(attr1);
        }

        if (!StringUtils.isEmpty(attr4)) {
            sb.append("AND g.attr4 = ? ");
            params.add(attr4);
        }

        if (!StringUtils.isEmpty(number)) {
            sb.append("AND g.number LIKE ? ");
            params.add("%" + number + "%");
        }


        if(categoryId!=null && categoryId.intValue() > 0) {
            //按一级大类查询
            sb.append(" AND (g.categoryId = ? OR g.categoryId IN (SELECT id FROM erp_goods_category WHERE parent_id = ?) )");
            params.add(categoryId);
            params.add(categoryId);
        }
        if(status!=null && status.intValue() > 0) {
            //按一级大类查询
            sb.append(" AND g.status = ? ");
            params.add(status);
        }
        if(contactId!=null && contactId>0){
            sb.append(" AND g.erpContactId = ? ");
            params.add(contactId);
        }

//        if(StringUtils.isEmpty(str) && StringUtils.isEmpty(categoryId) && StringUtils.isEmpty(status)){
//            return new PagingResponse<>(pageIndex, pageSize, 0, list);
//        }
        sb.append("ORDER BY g.id DESC LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);
        list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ErpGoodsListVo.class), params.toArray(new Object[params.size()]));
        int totalSize = getTotalSize();
        for (var item : list) {
            String sql1="SELECT s.*,IFNULL((SELECT IFNULL(SUM(currentQty),0) FROM erp_goods_stock_info WHERE specId=s.id AND isDelete=0),0) AS qty," +
                        "IFNULL((SELECT IFNULL(SUM(lockedQty),0) FROM erp_goods_stock_info WHERE specId=s.id AND isDelete=0),0) AS lockedQty " +
                        " FROM " + Tables.ErpGoodsSpec + " s WHERE goodsId=?";

            var specs = jdbcTemplate.query(sql1, new BeanPropertyRowMapper<>(ErpGoodsSpecStockVo.class), item.getId());
            item.setSpecList(specs);
        }

        return new PagingResponse<>(pageIndex, pageSize, totalSize, list);
    }

    public List<ErpGoodsListVo> getGoodsListExport(String number,Integer categoryId,Integer status,String attr1,String attr4) {
        List<ErpGoodsListVo> list = new ArrayList<>();
        StringBuilder sb = getGoodsListSQL();
        List<Object> params = new ArrayList<>();

        if (!StringUtils.isEmpty(attr1)) {
            sb.append("AND g.attr1 = ? ");
            params.add(attr1);
        }

        if (!StringUtils.isEmpty(attr4)) {
            sb.append("AND g.attr4 = ? ");
            params.add(attr4);
        }

        if (!StringUtils.isEmpty(number)) {
            sb.append("AND g.number LIKE ? ");
            params.add("%" + number + "%");
        }


        if(categoryId!=null && categoryId.intValue() > 0) {
            //按一级大类查询
            sb.append(" AND (g.categoryId = ? OR g.categoryId IN (SELECT id FROM erp_goods_category WHERE parent_id = ?) )");
            params.add(categoryId);
            params.add(categoryId);
        }
        if(status!=null && status.intValue() > 0) {
            //按一级大类查询
            sb.append(" AND g.status = ? ");
            params.add(status);
        }

//        if(StringUtils.isEmpty(str) && StringUtils.isEmpty(categoryId) && StringUtils.isEmpty(status)){
//            return new PagingResponse<>(pageIndex, pageSize, 0, list);
//        }
        sb.append("ORDER BY g.id DESC ");
        list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ErpGoodsListVo.class), params.toArray(new Object[params.size()]));

//        for (var item : list) {
//            var specs = jdbcTemplate.query("SELECT s.*,IFNULL((SELECT IFNULL(SUM(currentQty),0) FROM erp_goods_stock_info WHERE specId=s.id AND isDelete=0),0) AS qty,IFNULL((select SUM(quantity) from erp_order_item WHERE skuId=s.id AND (STATUS=0 OR STATUS=1)),0) lockedQty,(SELECT IFNULL(SUM(combo_num),0) from dc_douyin_orders_items i LEFT JOIN dc_douyin_orders o ON i.dc_douyin_orders_id=o.id WHERE (o.order_status=2 AND o.auditStatus=0) AND i.erpGoodsSpecId=s.id) as lockedQty1 FROM " + Tables.ErpGoodsSpec + " s WHERE goodsId=?", new BeanPropertyRowMapper<>(ErpGoodsSpecStockVo.class), item.getId());
//            item.setSpecList(specs);
//        }

        return list;
    }

    /**
     * 商品列表查询
     * @param categoryId 商品分类id
     * @return
     */
    public ErpGoodsListVo getGoodsList_1(Integer categoryId,String attr1,String attr2,String attr4) {
        List<ErpGoodsListVo> list = new ArrayList<>();
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT SUM(egsi.currentQty) qty,egc.name as category,e.attr1,e.attr2,e.attr4 FROM erp_goods e LEFT JOIN erp_goods_stock_info egsi ON e.id=egsi.goodsId  LEFT JOIN erp_goods_category egc on egc.id=e.categoryId ");


        sb.append(" WHERE e.isDelete=0   ");
        List<Object> params = new ArrayList<>();

        if (!StringUtils.isEmpty(attr1)) {
            sb.append("AND e.attr1 = ? ");
            params.add(attr1);
        }

        if (!StringUtils.isEmpty(attr2)) {
            sb.append("AND e.attr2 = ? ");
            params.add(attr2);
        }

        if (!StringUtils.isEmpty(attr4)) {
            sb.append("AND e.attr4 = ? ");
            params.add(attr4);
        }
        if(categoryId!=null && categoryId.intValue() > 0) {
            //按一级大类查询
            sb.append(" AND (e.categoryId = ? OR e.categoryId IN (SELECT id FROM erp_goods_category WHERE parent_id = ?) )");
            params.add(categoryId);
            params.add(categoryId);
        }
        return jdbcTemplate.queryForObject(sb.toString(), new BeanPropertyRowMapper<>(ErpGoodsListVo.class), params.toArray(new Object[params.size()]));
    }

    /**
     * 商品列表查询,不分页
     *
     * @param str
     * @return
     */
//    public List<ErpInviceListVo> getGoodsListN(String str) {
//        StringBuffer sb = new StringBuffer();
//        sb.append("SELECT SQL_CALC_FOUND_ROWS ");
//        sb.append(" g.id,g.name,g.number,g.status,cate.name as category,u.name as unitName,g.disable,");
//        sb.append("g.locationId,sh.name as locationName,g.attr1,g.attr2,g.attr3,  ");
//        sb.append("A.specNumber,A.color_value,A.size_value,A.style_value,");
//        sb.append("IFNULL((SELECT IFNULL(SUM(currentQty),0) FROM erp_goods_stock_info WHERE specId=A.id AND isDelete=0),0) as currentQty, ");
//        sb.append(" IFNULL((SELECT SUM(it1.quantity) FROM erp_order_item AS it1 WHERE it1.skuId=A.id AND  it1.`status` <= 1 ),0) AS pickingQty ");
//        sb.append(" FROM ").append(Tables.ErpGoods).append(" as g ");
//        sb.append(" LEFT JOIN ").append(Tables.ErpGoodsCategory).append(" as cate on cate.id=g.categoryId ");
//        sb.append(" LEFT JOIN ").append(Tables.ErpUnit).append(" as u on u.id=g.unitId ");
//        sb.append(" LEFT JOIN ").append(Tables.ErpStockLocation).append(" as sh on sh.id=g.locationId ");
//        sb.append("LEFT JOIN  ").append(Tables.ErpGoodsSpec).append(" A ON A.goodsId=g.id");
//        sb.append(" WHERE g.isDelete=0 and currentQty>0 ");
//        List<Object> params = new ArrayList<>();
//        if (!StringUtils.isEmpty(str)) {
//            sb.append("AND g.number LIKE ? ");
//            params.add("%" + str + "%");
//        }
//        sb.append("ORDER BY g.id DESC ");
//        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ErpInviceListVo.class), params.toArray(new Object[params.size()]));
//    }

    /**
     * 获取分类json对象
     *
     * @return
     */
    public String getCategoryString() {
        //查询一级分类
        var list = jdbcTemplate.query("SELECT id,name FROM " + Tables.ErpGoodsCategory + " WHERE parent_id=0 AND isDelete=0", new BeanPropertyRowMapper<>(CategoryRe.class));
        for (var item: list) {
            List<CategoryRe> lists = getCategorys(item.getId());
            item.setChild(lists);
        }
        return JSON.toJSONString(list);
//        Integer id = jdbcTemplate.queryForObject("SELECT id FROM " + Tables.ErpGoodsCategory + " WHERE parent_id=0 AND isDelete=0", Integer.class);
//        List<CategoryRe> lists = getCategorys(id);
//        String re = JSON.toJSONString(lists);
//        return re;
    }

    /**
     * 迭代查分类数据
     *
     * @param parentId
     * @return
     */
    private List<CategoryRe> getCategorys(Integer parentId) {
        String sql = "SELECT * FROM " + Tables.ErpGoodsCategory + " WHERE parent_id = ? AND isDelete=0";
        List<GoodsCategoryEntity> entities = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(GoodsCategoryEntity.class), parentId);
        List<CategoryRe> vos = new ArrayList<>();
        for (GoodsCategoryEntity entity : entities) {
            CategoryRe vo = new CategoryRe();
            vo.setName(entity.getName());
            vo.setId(entity.getId());
            vo.setChild(getCategorys(entity.getId()));
            vos.add(vo);
        }
        return vos;
    }

    /**
     * 新增商品信息
     *
     * @param goodsName
     * @param number
     * @param categoryId
     * @param unitId
     * @return
     */
    public Integer saveGoods(String goodsName, String number, Integer categoryId, Integer unitId) {
        String goodsInsert = "INSERT INTO " + Tables.ErpGoods + " (name,number,categoryId,categoryName,unitId,unitName) VALUES (?,?,?,?,?,?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(goodsInsert, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, goodsName);
            ps.setString(2, number);
            ps.setInt(3, categoryId);
            ps.setString(4, getCategoryNameById(categoryId));
            ps.setInt(5, unitId);
            ps.setString(6, getUnitNameById(unitId));
            return ps;
        }, keyHolder);

        return keyHolder.getKey().intValue();
    }

    /**
     * 更新商品信息
     *
     * @param goodsName
     * @param categoryId
     * @param unitId
     * @param goodsId
     */
    public void updateGoods(String goodsName, Integer categoryId, Integer unitId, Integer locationId, Integer reservoirId, Integer shelfId, Integer goodsId) {
        //查询仓库
        var stockHouse = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpStockLocation + " WHERE id=?", new BeanPropertyRowMapper<>(ErpStockLocationEntity.class), locationId);
        String houseName = stockHouse.getName();
        //查询库区
        var reservoir = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpStockLocation + " WHERE id=?", new BeanPropertyRowMapper<>(ErpStockLocationEntity.class), reservoirId);
        String reservoirName = reservoir.getName();
        //查询库区
        var shelf = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpStockLocation + " WHERE id=?", new BeanPropertyRowMapper<>(ErpStockLocationEntity.class), shelfId);
        String shelfName = shelf.getName();
        String goodsUpdate = "UPDATE " + Tables.ErpGoods + " SET name=?,categoryId=?,categoryName=?,unitId=?,unitName=?,locationId=?,locationName=?,reservoirId=?,reservoirName=?,shelfId=?,shelfName=? WHERE id=? AND isDelete=0";
        jdbcTemplate.update(goodsUpdate, goodsName, categoryId, getCategoryNameById(categoryId), unitId, getUnitNameById(unitId), locationId, houseName, reservoirId, reservoirName, shelfId, shelfName, goodsId);
    }

    /**
     * 根据id查询计量单位名称
     *
     * @param unitId
     * @return
     */
    public String getUnitNameById(Integer unitId) {
        return jdbcTemplate.queryForObject("SELECT number FROM " + Tables.ErpUnit + " WHERE id=?", String.class, unitId);
    }

    /**
     * 通过分类id查询分类名
     *
     * @param id
     * @return
     */
    public String getCategoryNameById(Integer id) {
        try {
            return jdbcTemplate.queryForObject("SELECT number FROM " + Tables.ErpGoodsCategory + " WHERE id=?", String.class, id);
        } catch (EmptyResultDataAccessException e) {
            return "";
        }
    }

    /**
     * 保存商品规格
     *
     * @param specNumber
     * @param specName
     * @param lowQty
     * @param highQty
     * @param goodsId
     * @return
     */
    public Integer saveGoodsSpec(String specNumber, String specName, String lowQty, String highQty, Integer goodsId, Integer locationId) {
        String specInsert = "INSERT INTO " + Tables.ErpGoodsSpec + " (goodsId,specName,specNumber,lowQty,highQty,locationId) VALUES (?,?,?,?,?,?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(specInsert, Statement.RETURN_GENERATED_KEYS);
            ps.setInt(1, goodsId);
            ps.setString(2, specName);
            ps.setString(3, specNumber);
            ps.setString(4, lowQty);
            ps.setString(5, highQty);
            ps.setInt(6, locationId);
            return ps;
        }, keyHolder);
        return keyHolder.getKey().intValue();
    }

    /**
     * 更新商品规格
     *
     * @param specName
     * @param lowQty
     * @param highQty
     * @param specId
     */
    public void updateGoodsSpec(String specName, String lowQty, String highQty, Integer specId, Integer locationId) {
        jdbcTemplate.update("UPDATE " + Tables.ErpGoodsSpec + " SET specName=?,lowQty=?,highQty=?,locationId=? WHERE id=? AND isDelete=0", specName, lowQty, highQty, locationId, specId);
    }

    /**
     * 商品编码是否存在
     *
     * @param number
     * @return 商品id
     */
    public Integer ifExsitNumber(String number) {
        Integer num = 0;
        try {
            num = jdbcTemplate.queryForObject("SELECT id FROM " + Tables.ErpGoods + " WHERE number = ? AND isDelete=0", Integer.class, number);
        } catch (EmptyResultDataAccessException e) {
            num = 0;
        }
        return num;
    }

    /**
     * 规格编码是否存在
     *
     * @param specNumber
     * @return
     */
    public Integer ifExsitSpecNumber(String specNumber) {
        Integer num = 0;
        try {
            num = jdbcTemplate.queryForObject("SELECT id FROM " + Tables.ErpGoodsSpec + " WHERE specNumber = ? AND isDelete=0", Integer.class, specNumber);
        } catch (EmptyResultDataAccessException e) {
            num = 0;
        }
        return num;
    }

    /**
     * 删除商品规格
     *
     * @return
     */
//    public void delSpec(Integer specId) {
//        Integer goodsId = jdbcTemplate.queryForObject("SELECT goodsId FROM " + Tables.ErpGoodsSpec + " WHERE id = ? ", Integer.class, specId);
//        Integer num = jdbcTemplate.queryForObject("SELECT IFNULL(count(id),0) FROM " + Tables.ErpGoodsSpec + " WHERE goodsId = ? AND isDelete=0", Integer.class, goodsId);
//        //删除规格
//        jdbcTemplate.update("UPDATE " + Tables.ErpGoodsSpec + " SET isDelete=1 WHERE id=?", specId);
//        if (num == 1) {
//            //删除商品
//            jdbcTemplate.update("UPDATE " + Tables.ErpGoods + " SET isDelete=1 WHERE id=?", goodsId);
//        }
//    }
    public void deleteGoods(Integer goodsId) {
        jdbcTemplate.update("update " + Tables.ErpGoods + " set isDelete=1 where id=?", goodsId);
        jdbcTemplate.update("update " + Tables.ErpGoodsSpec + " set isDelete=1 where goodsId=?", goodsId);
    }


    /**根据Id查看库存**/
    public Long getGooodsById(Integer goodsId){
        try {
            return jdbcTemplate.queryForObject("select  IFNULL(SUM(currentQty),0) from erp_goods_spec where goodsId=?",Long.class,goodsId);
        }catch (Exception e){
            return 0L;
        }
    }

    /**
     * 通过名称和父类id查询该名称是否已存在
     *
     * @param id
     * @param name
     * @return
     */
    public Integer getCateIdByNameAndParentId(Integer id, String name) {
        return jdbcTemplate.queryForObject("SELECT IFNULL(count(id),0) FROM " + Tables.ErpGoodsCategory + " WHERE parent_id=? AND name=? AND isDelete=0", Integer.class, id, name);
    }

    /**
     * 查询计量表
     *
     * @return
     */
    public List<UnitEntiy> getUnitList() {
        return jdbcTemplate.query("SELECT * FROM " + Tables.ErpUnit+ " order by isDefault asc", new BeanPropertyRowMapper<>(UnitEntiy.class));
    }

    /**
     * 根据id查询
     *
     * @param id
     * @return
     */
    public SpecRe getSpeckListById(Integer id) {
        SpecRe re = new SpecRe();
        re.setId(id);
        jdbcTemplate.query("SELECT number,name,categoryName,categoryId,unitId,unitName FROM " + Tables.ErpGoods + " WHERE id=? AND isDelete=0 ", resultSet -> {
            re.setName(resultSet.getString("name"));
            re.setNumber(resultSet.getString("number"));
            re.setCategoryName(resultSet.getString("categoryName"));
            re.setCategoryId(resultSet.getInt("categoryId"));
            re.setUnitId(resultSet.getInt("unitId"));
            re.setUnitName(resultSet.getString("unitName"));
        }, id);
        List<SpecRe> list = jdbcTemplate.query("SELECT id,specName,specNumber,lowQty,highQty FROM " + Tables.ErpGoodsSpec + " WHERE goodsId=? AND isDelete=0 ", resultSet -> {
            List<SpecRe> vos = new ArrayList<>();
            while (resultSet.next()) {
                SpecRe vo = new SpecRe();
                vo.setId(resultSet.getInt("id"));
                vo.setSpecName(resultSet.getString("specName"));
                vo.setSpecNumber(resultSet.getString("specNumber"));
                vo.setLowQty(resultSet.getString("lowQty"));
                vo.setHighQty(resultSet.getString("highQty"));
                vos.add(vo);
            }
            return vos;
        }, id);
//        re.setDetail(JSON.toJSONString(list));
        re.setDetail(list);
        return re;
    }

    /**
     * 仓库查询表插入数据
     *
     * @param goodsId
     * @param specNumber
     * @param locationId
     * @return
     */
//    public Integer saveGoodsStockInfo(Integer goodsId, String goodsNumber, Integer specId, String specNumber, Integer locationId) {
//        String specInsert = "INSERT INTO " + Tables.ErpGoodsStockInfo + " (goodsId,goodsNumber,specId,specNumber,locationId) VALUES (?,?,?,?,?)";
//        KeyHolder keyHolder = new GeneratedKeyHolder();
//        jdbcTemplate.update(connection -> {
//            PreparedStatement ps = connection.prepareStatement(specInsert, Statement.RETURN_GENERATED_KEYS);
//            ps.setInt(1, goodsId);
//            ps.setString(2, goodsNumber);
//            ps.setInt(3, specId);
//            ps.setString(4, specNumber);
//            ps.setInt(5, locationId);
//            return ps;
//        }, keyHolder);
//        return keyHolder.getKey().intValue();
//    }

    /**
     * 仓库查询表更新数据
     * @param specNumber
     * @param infoId
     */
    /*public void updateGoodsStockInfo(String specNumber, Integer infoId){
        String specUpdate = "UPDATE "+ Tables.ErpGoodsStockInfo +" SET specNumber=? WHERE id=?";
        jdbcTemplate.update(specUpdate,specNumber,infoId);
    }*/

    /**
     * 库存查询
     *
     * @param pageIndex
     * @param pageSize
     * @param locationNum 仓位编码
     * @param houseId
     * @return
     */
    public PagingResponse<ErpGoodsSpecStockVo> goodsStockInfoSearch(int pageIndex, int pageSize, String locationNum, Integer houseId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS A.*,B.name as goodsName,B.number,B.categoryId,C.color_value,C.size_value,C.style_value,D.number as locationNumber ");
        sb.append(" FROM " + Tables.ErpGoodsStockInfo + " A ");
        sb.append(" LEFT JOIN " + Tables.ErpGoods + " B ON B.id = A.goodsId ");
        sb.append(" LEFT JOIN " + Tables.ErpGoodsSpec + " C ON C.id = A.specId ");
        sb.append(" LEFT JOIN " + Tables.ErpStockLocation + " D ON D.id = A.locationId ");

        sb.append("WHERE A.isDelete=0 AND C.isDelete=0 ");
        List<Object> params = new ArrayList<>();
        if (!StringUtils.isEmpty(locationNum)) {
            sb.append("AND D.number =? ");
            params.add(locationNum);
        }

        if (houseId > 0) {
            sb.append("AND D.parentId1=? ");
            params.add(houseId);
        }

        sb.append("ORDER BY A.id DESC LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);
        List<ErpGoodsSpecStockVo> list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ErpGoodsSpecStockVo.class), params.toArray(new Object[params.size()]));

        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), list);
    }

    /**
     * 商品SKU库存查询
     *
     * @param pageIndex
     * @param pageSize
     * @param number sku编码
     * @param filter0 是否过滤0数据
     * @param categoryId 商品分类id
     * @return
     */
    @Transactional
    public PagingResponse<ErpGoodsSpecStockVo> goodsSkuSearch(int pageIndex, int pageSize, String number,int filter0,Integer categoryId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS A.id,A.specNumber,A.color_image,A.color_value,A.size_value,A.purPrice,B.`name` goodsName,B.number,unit.name as unitName,B.image, ");
        sb.append(" IFNULL((SELECT IFNULL(SUM(currentQty),0) FROM erp_goods_stock_info WHERE specId=A.id AND isDelete=0),0) AS qty ");
        sb.append("FROM ").append(Tables.ErpGoodsSpec).append(" A ");
        sb.append(" LEFT JOIN  ").append(Tables.ErpGoods).append(" B ON B.id=A.goodsId ");
        sb.append(" LEFT JOIN  ").append(Tables.ErpUnit).append(" unit ON unit.id = B.unitId ");
        sb.append("WHERE B.isDelete=0 ");

        //统计总库存
        StringBuilder tjSQL = new StringBuilder();
        tjSQL.append("SELECT IFNULL(SUM(egsi.currentQty),0) FROM erp_goods_stock_info egsi ");
        tjSQL.append(" LEFT JOIN  ").append(Tables.ErpGoodsSpec).append(" A ON A.id=egsi.specId ");
        tjSQL.append(" LEFT JOIN  ").append(Tables.ErpGoods).append(" B ON B.id=A.goodsId ");
        tjSQL.append("WHERE egsi.isDelete=0 ");
        List<Object> tjParams = new ArrayList<>();

        List<Object> params = new ArrayList<>();
        if (!StringUtils.isEmpty(number)) {
            sb.append("AND A.specNumber LIKE ? ");
            params.add("%" + number + "%");


            tjSQL.append("AND (A.specNumber =? or B.number=?) ");
            tjParams.add(number);
            tjParams.add(number);

        }
//        if(filter0 == 1){
        //    sb.append(" AND qty > 0 ");

//        }

        if(categoryId!=null && categoryId.intValue() > 0) {
            //按一级大类查询
            sb.append(" AND (B.categoryId = ? OR B.categoryId IN (SELECT id FROM erp_goods_category WHERE parent_id = ?) )");
            params.add(categoryId);
            params.add(categoryId);

            tjSQL.append(" AND (B.categoryId = "+categoryId+" OR B.categoryId IN (SELECT id FROM erp_goods_category WHERE parent_id = "+categoryId+") )");
//            tjParams.add(categoryId);
//            tjParams.add(categoryId);
        }

        String desc=filter0 == 1 ? " qty DESC " : " A.id DESC ";
        sb.append("ORDER BY  ");
        sb.append(desc);
        sb.append("LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);
        long total = jdbcTemplate.queryForObject(tjSQL.toString(), Long.class,tjParams.toArray(new Object[tjParams.size()]));

        List<ErpGoodsSpecStockVo> list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ErpGoodsSpecStockVo.class), params.toArray(new Object[params.size()]));
        int totalSize = getTotalSize();

        for (ErpGoodsSpecStockVo item : list){
            String sql = "SELECT A.specId,B.name as locationName,A.currentQty,A.lockedQty " +
                    "FROM erp_goods_stock_info A LEFT JOIN erp_stock_location B ON B.id =A.locationId WHERE A.specId=? AND A.isDelete=0";
            item.setStockList(jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(ErpGoodsSpecStockListVo.class),item.getId()));
        }
        var result =new PagingResponse<>(pageIndex, pageSize,totalSize, list);
        result.setData2(total);
        return result;
    }


    @Transactional
    public List<ErpGoodsSpecStockVo> goodsSkuListExport() {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS A.id,A.specNumber,A.color_image,A.color_value,A.size_value,B.`name` goodsName,B.number,B.image,B.cost_price, ");
        sb.append(" IFNULL((SELECT IFNULL(SUM(currentQty),0) FROM erp_goods_stock_info WHERE specId=A.id AND isDelete=0),0) AS qty ");
        sb.append(" FROM ").append(Tables.ErpGoodsSpec).append(" A ");
        sb.append(" LEFT JOIN  ").append(Tables.ErpGoods).append(" B ON B.id=A.goodsId ");
        sb.append("WHERE B.isDelete=0 ");
        List<Object> params = new ArrayList<>();
        String desc = " A.id DESC ";
        sb.append("ORDER BY  ");
        sb.append(desc);

        List<ErpGoodsSpecStockVo> list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ErpGoodsSpecStockVo.class), params.toArray(new Object[params.size()]));

        return list;
    }



    /**
     * 商品SKU库存查询记录
     * @param pageIndex
     * @param pageSize
     * @param number
     * @return
     */
    public PagingResponse<ErpGoodsStockLogsListVo> goodsSkuSearchLogs(int pageIndex, int pageSize, String number) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT A.*,B.`name` locationNumber,C.`name` goodsName,D.color_value,D.size_value " +
                "FROM erp_goods_stock_logs A " +
                " LEFT JOIN erp_stock_location B ON A.locationId=B.id " +
                " LEFT JOIN erp_goods C ON C.id=A.goodsId " +
                " LEFT JOIN erp_goods_spec D ON D.id=A.specId   ");
        List<Object> params = new ArrayList<>();
        sb.append("WHERE  A.specNumber = ? ");
        params.add(number);

        sb.append("ORDER BY A.id DESC LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        List<ErpGoodsStockLogsListVo> list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ErpGoodsStockLogsListVo.class), params.toArray(new Object[params.size()]));
        return new PagingResponse<>(pageIndex, pageSize,getTotalSize(), list);
    }
    /**
     * 库存查询,不分页
     *
     * @param str
     * @param houseId
     * @return
     */
    public List<ErpGoodsSpecStockVo> goodsStockInfoSearch(String str, Integer houseId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS A.*,B.name as goodsName,B.number,B.categoryId,B.unitName,C.color_value,C.size_value,C.style_value,D.name locationName ");
        sb.append("FROM " + Tables.ErpGoodsStockInfo + " A ");
        sb.append(" LEFT JOIN " + Tables.ErpGoods + " B ON B.id = A.goodsId ");
        sb.append("LEFT JOIN " + Tables.ErpGoodsSpec + " C ON C.id = A.specId ");
        sb.append("LEFT JOIN " + Tables.ErpStockLocation + " D ON D.id = A.locationId ");

        sb.append("WHERE A.isDelete=0 AND C.isDelete=0 ");
        List<Object> params = new ArrayList<>();
        if (!StringUtils.isEmpty(str)) {
            sb.append("AND (A.specNumber = ? OR B.number = ? OR B.name LIKE ?) ");
            params.add(str);
            params.add(str);
            params.add("%" + str + "%");
        }
        if (houseId > 0) {
            sb.append("AND A.locationId=? ");
            params.add(houseId);
        }
        sb.append("ORDER BY A.specNumber DESC");
        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ErpGoodsSpecStockVo.class), params.toArray(new Object[params.size()]));
    }

    /**
     * 根据有赞id查询商品规格列表
     *
     * @param yzItemId
     * @return
     */
    public List<ErpGoodsSpecEntity> getGoodSpesByYzItemId(Long yzItemId) {
        String queryErpSpec = "select * from erp_goods_spec where yz_item_id=?";
        return jdbcTemplate.query(queryErpSpec, new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), yzItemId);
    }


    /**
     * 根据规格编码查询商品库区
     * @param locationId
     * @param rreservoirId
     * @param shelfId
     * @param number
     * @return
     */
    public List<ErpGoodsSpecVo> getGoodSpecByMoveSpec(Integer locationId, Integer rreservoirId, Integer shelfId, String number){
        List<Object> params = new ArrayList<>();
        StringBuilder builder = new StringBuilder("SELECT es.*,(SELECT name from "+Tables.ErpGoods+" WHERE id=eg.goodsId) name,concat(eg.color_value,'(',eg.size_value,')') specName FROM erp_goods_stock_info  es LEFT JOIN erp_goods_spec eg ON es.specNumber=eg.specNumber ");
        builder.append(" LEFT JOIN erp_stock_location esl ON es.locationId=esl.id ");
        builder.append(" where es.isDelete=0 and (esl.parentId1=? and esl.parentId=? and esl.id=? ) ");
        params.add(locationId);
        params.add(rreservoirId);
        params.add(shelfId);
        if (!StringUtils.isEmpty(number)) {
            builder.append("AND es.specNumber LIKE ? ");
            params.add("%" + number + "%");
        }
        return jdbcTemplate.query(builder.toString(), new BeanPropertyRowMapper<>(ErpGoodsSpecVo.class), params.toArray());
    }

    /**
     * 添加商品移库单
     *
     * @param ids  erp_goods_stock_info.id
     * @param number      移库数量
     * @param moveNo      单号
     * @param remarks     备注
     * @param createBy    创建人
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Integer> addGoodsSpecMoveForm(String[] ids,String[] number,String[] remarks, Integer outLocaltionId, Integer inLocationId, String moveNo,  String createBy) {
        if(ids==null || ids.length==0){
            return new ResultVo<>(EnumResultVo.ParamsError,"数据错误，没有选择移出的sku");
        }
        //判断移出移入仓位
        if(outLocaltionId.intValue() == inLocationId.intValue()){
            return new ResultVo<>(EnumResultVo.ParamsError,"数据错误，移入移出仓位不能一致");
        }
        //判断移入在仓位是否存在
        try {
            var inLocation = jdbcTemplate.query("select id from " + Tables.ErpStockLocation + " WHERE id=?", new BeanPropertyRowMapper<>(ErpStockLocationEntity.class), inLocationId);
        }
        catch (Exception e){
            return new ResultVo<>(EnumResultVo.ParamsError,"移入仓位不存在");
        }

        try {
            for (int i = 0, n = ids.length; i < n; i++) {
                //判断移出的数据
                var oldStockInfo = jdbcTemplate.queryForObject("SELECT * FROM "+Tables.ErpGoodsStockInfo + " WHERE id=?",new BeanPropertyRowMapper<>(ErpGoodsStockInfoEntity.class),ids[i]);

                //判断移出的仓位Id
                if(outLocaltionId.intValue()!=oldStockInfo.getLocationId().intValue()){
                    return new ResultVo<>(EnumResultVo.ParamsError,"数据错误，移出的仓位中不存在该sku");
                }

                //判断最大可移出的数量
                Long quantity = Long.parseLong(number[i]);//操作的数量
                if(oldStockInfo.getCurrentQty().longValue() < quantity.longValue()){
                    return new ResultVo<>(EnumResultVo.ParamsError,oldStockInfo.getSpecNumber()+",移库数量大于库存数量");
                }

                //判断移入的仓位是否被使用
                var inLocationStock = jdbcTemplate.query("SELECT * FROM "+Tables.ErpGoodsStockInfo +" where locationId=? AND isDelete=0 AND specId<>?"
                        ,new BeanPropertyRowMapper<>(ErpGoodsStockInfoEntity.class),inLocationId,oldStockInfo.getSpecId());
                if(inLocationStock!=null && inLocationStock.size()>0){
                    return new ResultVo<>(EnumResultVo.ParamsError,"数据错误，移入的仓位已经被占用");
                }

                //查询自己
                try{
                    var newStock = jdbcTemplate.queryForObject("SELECT * FROM "+Tables.ErpGoodsStockInfo +" where locationId=? AND isDelete=0 AND specId=?"
                            ,new BeanPropertyRowMapper<>(ErpGoodsStockInfoEntity.class),inLocationId,oldStockInfo.getSpecId());
                    //存在，更新新仓位库存
                    jdbcTemplate.update("update "+Tables.ErpGoodsStockInfo+" set currentQty=currentQty+? where id=?",quantity,newStock.getId());

                }catch (Exception e){
                    //不存在，插入新仓位库存
                    String addErpGoodsStockSQL="insert into "+Tables.ErpGoodsStockInfo+" set goodsId=?,goodsNumber=?,specId=?,specNumber=?,locationId=?,currentQty=?,isDelete=0";
                    jdbcTemplate.update(addErpGoodsStockSQL,oldStockInfo.getGoodsId(),oldStockInfo.getGoodsNumber()
                            ,oldStockInfo.getSpecId(),oldStockInfo.getSpecNumber(),inLocationId,quantity);
                }

                //减旧仓位库存
                jdbcTemplate.update("update "+Tables.ErpGoodsStockInfo+" set currentQty=currentQty-? where id=?",quantity,oldStockInfo.getId());
                //移出全部更新旧仓位数据
                if(oldStockInfo.getCurrentQty().longValue() == quantity.longValue()){
                    jdbcTemplate.update("update "+Tables.ErpGoodsStockInfo+" set isDelete=1 where id=?",oldStockInfo.getId());
                }
                var oldLocation = jdbcTemplate.queryForObject("select * from "+Tables.ErpStockLocation+" where id=?",new BeanPropertyRowMapper<>(ErpStockLocationEntity.class),outLocaltionId);
                var newLocation = jdbcTemplate.queryForObject("select * from "+Tables.ErpStockLocation+" where id=?",new BeanPropertyRowMapper<>(ErpStockLocationEntity.class),inLocationId);
                /**添加移库记录--erp_goods_spec_move_form**/
                String addErpGoodsSpecMoveFormSQL = "insert "+Tables.ErpGoodsSpecMoveFrom+" set specId=?,moveNo=?,quantity=?,createTime=unix_timestamp(now()),createBy=?,oldLocationId=?,oldReservoirId=?,oldShelfId=?,newLocationId=?,newReservoirId=?,newShelfId=?,remarks=?";
                jdbcTemplate.update(addErpGoodsSpecMoveFormSQL,oldStockInfo.getSpecId(),moveNo,quantity,createBy,oldLocation.getParentId1(),oldLocation.getParentId(),outLocaltionId,newLocation.getParentId1(),newLocation.getParentId(),inLocationId,remarks[i]);

                /**移库日志**/
                String remark = "商品移库，移库单号：" + moveNo;
                remark += " SKU：" + oldStockInfo.getSpecNumber();
                String oldSheft =this.getSheftNameById(outLocaltionId);
                String newSheft =this.getSheftNameById(inLocationId);
                remark+="."+oldSheft+"移动："+quantity+"至"+newSheft;
                String logsSQL = "INSERT INTO " + Tables.ErpGoodsStockLogs +
                        " (goodsId,goodsNumber,specId,specNumber,locationId,quantity,createTime,type,sourceType,sourceId,remark,createOn) " +
                        " VALUE (?,?,?,?,?,?,?,?,?,?,?,?)";

                jdbcTemplate.update(logsSQL,
                        oldStockInfo.getGoodsId(), oldStockInfo.getGoodsNumber(), oldStockInfo.getSpecId(),
                        oldStockInfo.getSpecNumber(),outLocaltionId,quantity,
                        new Date(), EnumGoodsStockLogType.IN.getIndex(),
                        EnumGoodsStockLogSourceType.MOVE_IN.getIndex(),0, remark,System.currentTimeMillis() / 1000);
            }
            return  new ResultVo<>(EnumResultVo.SUCCESS,"移动成功");
        }catch (Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return  new ResultVo<>(EnumResultVo.Fail,"移库异常："+e.getMessage());
        }

    }
    //根据货架Id查询货架名
    public String getSheftNameById(Integer id){
        try {
            return jdbcTemplate.queryForObject("SELECT number FROM erp_stock_location WHERE id= ? ", String.class, id);
        }catch (Exception e){
            return "";
        }

    }

    /**
     * 移库列表
     *
     * @param pageIndex
     * @param pageSize
     * @return
     */
    public PagingResponse<ErpGoodsSpecMoveFromEntiy> getStockHouseVo(Integer pageIndex, Integer pageSize,String moveNo) {
        StringBuilder builder = new StringBuilder("SELECT SQL_CALC_FOUND_ROWS  * FROM " + Tables.ErpGoodsSpecMoveFrom + " where 1=1 ");
        List<Object> params = new ArrayList<>();
        if(StringUtils.isEmpty(moveNo)==false){
            builder.append( "and moveNo like ?");
            params.add("%" + moveNo + "%");
        }
        builder.append(" ORDER BY id DESC LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);
        var list= jdbcTemplate.query(builder.toString(),new BeanPropertyRowMapper<>(ErpGoodsSpecMoveFromEntiy.class),params.toArray());
        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), list);
    }
    /**
     * 根据Id查询移库信息
     *
     * @param id
     * @return
     */
    public ErpGoodsSpecMoveFromEntiy getIdByErpGoodsSpecMoveFromEntiy(Integer id) {
        String erpGoodSpecMoveSQL="SELECT egsm.*,(SELECT es.name FROM "+Tables.ErpGoods+" es LEFT JOIN erp_goods_spec esc ON es.id=esc.goodsId WHERE esc.id=egsm.specId) goodsName,(SELECT concat(esc.color_value,'(',esc.size_value,')') FROM "+Tables.ErpGoodsSpec+" esc WHERE esc.id=egsm.specId) specName,(SELECT specNumber FROM erp_goods_spec esc WHERE esc.id=egsm.specId) specNumber from " + Tables.ErpGoodsSpecMoveFrom + " egsm where id=?";
        return jdbcTemplate.queryForObject(erpGoodSpecMoveSQL, new BeanPropertyRowMapper<>(ErpGoodsSpecMoveFromEntiy.class), id);
    }

    /**
     * 根据商品id查询商品规格列表
     * @param goodId
     * @return
     */
    public List<ErpGoodsSpecVo> getGoodSpecByGoodId(Integer goodId) {
        String erpGoodSepecSQL="SELECT egs.id,egs.specNumber,(SELECT name from "+Tables.ErpGoods+" WHERE id=egs.goodsId) name,concat(egs.color_value,'(',egs.size_value,')') specName FROM "+Tables.ErpGoodsSpec+" egs WHERE egs.goodsId=?";
        return jdbcTemplate.query(erpGoodSepecSQL, new BeanPropertyRowMapper<>(ErpGoodsSpecVo.class),goodId);
    }

    /**
     * 根据id更新规格信息
     * @param oldId
     * @param newId
     * @return
     */
    @Transactional(rollbackFor=Exception.class)
    public ResultVo<Integer> updErpGoodSpecById(Integer oldId, Integer newId){
        try {
            var oldGoodSpec=jdbcTemplate.queryForObject("select * from "+Tables.ErpGoodsSpec+" where id=?",new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class),oldId);
            var newGoodSpec=jdbcTemplate.queryForObject("select * from "+Tables.ErpGoodsSpec+" where id=?",new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class),newId);
            Integer countStock = jdbcTemplate.queryForObject("select count(1) from "+Tables.ErpGoodsStockInfo+" where goodsId=? and specNumber=?",Integer.class,newGoodSpec.getGoodsId(),newGoodSpec.getSpecNumber());
            if(newGoodSpec.getLocationId().intValue()>0 || countStock>0)return new ResultVo<>(EnumResultVo.NotFound,"选中变更后的规格已存在库存,请重新选择");

            /**更新新规格属性到旧规格--erp_goods_spec**/
            String updErpGoodsSepcSQL="update "+Tables.ErpGoodsSpec+" set specNumber=?,color_id=?,color_value=?,size_id=?,size_value=? where id=?";
            jdbcTemplate.update(updErpGoodsSepcSQL,newGoodSpec.getSpecNumber(),newGoodSpec.getColorId(),newGoodSpec.getColorValue(),newGoodSpec.getSizeId(),newGoodSpec.getSizeValue(),oldId);

            /**更新新规格属性到旧规格库存--erp_goods_stock_info**/
            String updErpGoodsStockInfoSQL="update "+Tables.ErpGoodsStockInfo+" set specNumber=? where goodsId=? and specId=?";
            jdbcTemplate.update(updErpGoodsStockInfoSQL,newGoodSpec.getSpecNumber(),oldGoodSpec.getGoodsId(),oldGoodSpec.getId());

            /**删除新规格信息**/
            jdbcTemplate.update("delete  from "+Tables.ErpGoodsSpec+" where id=?",newId);
            jdbcTemplate.update("delete from "+Tables.ErpGoodsSpecAttr+" where type=? and v=?","color",oldGoodSpec.getColorValue());

            return new ResultVo<>(EnumResultVo.SUCCESS,"成功");
        }catch (Exception e){
            return new ResultVo<>(EnumResultVo.NotFound,"商品规格信息不存在");
        }
    }

    /**
     * 根据sku编码搜索商品库存信息
     * @param number
     * @return
     */
    public List<ErpGoodsSpecStockListVo> searchGoodSpecStockByNumber(String number){
        String sql = "SELECT A.id,gs.specNumber,g.id as goodsId,g.number as goodsNumber,gs.id as goodsSpecId,gs.color_value,gs.size_value,B.name as locationName,A.locationId,A.currentQty " +
                "FROM erp_goods_stock_info A " +
                " LEFT JOIN erp_stock_location B ON B.id =A.locationId " +
                " LEFT JOIN erp_goods_spec gs on gs.id = A.specId"+
                " LEFT JOIN erp_goods g on g.id = gs.goodsId"+
                " WHERE gs.specNumber LIKE ? AND A.isDelete=0 LIMIT 10 ";
        List<Object> params = new ArrayList<>();
        if (!StringUtils.isEmpty(number)) {
            params.add("%" + number + "%");
        }
         return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(ErpGoodsSpecStockListVo.class),params.toArray(new Object[params.size()]));

    }

    /**
     * 根据商品一级分类获取总库存
     * @param categoryId 商品一级分类
     * @return
     */
//    public ResultVo<Long> getStockQtyByGoodsCategory(Integer categoryId) {
//        //统计总库存
//        StringBuilder tjSQL = new StringBuilder();
//        tjSQL.append("SELECT IFNULL(SUM(egsi.currentQty),0) FROM erp_goods_stock_info egsi ");
//        tjSQL.append(" LEFT JOIN  ").append(Tables.ErpGoodsSpec).append(" A ON A.id=egsi.specId ");
//        tjSQL.append(" LEFT JOIN  ").append(Tables.ErpGoods).append(" B ON B.id=A.goodsId ");
//        tjSQL.append("WHERE B.isDelete=0 ");
//        List<Object> tjParams = new ArrayList<>();
//
//        if(categoryId!=null && categoryId.intValue() > 0) {
//            //按一级大类查询
//            tjSQL.append(" AND (B.categoryId = "+categoryId+" OR B.categoryId IN (SELECT id FROM erp_goods_category WHERE parent_id = "+categoryId+") )");
//            tjParams.add(categoryId);
//            tjParams.add(categoryId);
//        }
//
//        long total = jdbcTemplate.queryForObject(tjSQL.toString(), Long.class);
//        return new ResultVo<>(EnumResultVo.SUCCESS,total);
//    }

    /**
     * 获取分类库存统计
     * @return
     */
    public List<ErpGoodsSpecStockListVo> getStockQtyByGoodsCategory(){
        String sql = "SELECT gc.`name` ";
        sql+=", (SELECT IFNULL(SUM(egsi.currentQty),0) FROM erp_goods_stock_info egsi WHERE egsi.isDelete=0 AND egsi.goodsId IN (SELECT id FROM erp_goods WHERE categoryId=gc.id)) AS currentQty ";
        sql+=" FROM erp_goods_category gc ";
        sql+= " WHERE gc.parent_id <> 0 ";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(ErpGoodsSpecStockListVo.class));
    }

    public List<ErpGoodsSpecEntity> specStockByExport(){
        String sql = "SELECT egsi.goodsNumber,s.specNumber,s.color_value,s.size_value,egsi.currentQty,(SELECT IFNULL(SUM(purPrice*currentQty),0) from  erp_goods_stock_info_item WHERE stockInfoId=egsi.id AND currentQty>0) amount  from erp_goods_spec s LEFT JOIN erp_goods_stock_info egsi ON s.id =egsi.specId\n" +
                " WHERE egsi.isDelete=0 AND egsi.currentQty>0 ";

        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class));

    }


}
